﻿/* 
 * POSTGRESHR - MARC-HI SHARED SCHEMA - QUICK AND DIRTY CODE RESOLVER
 * VERSION: 3.0
 * AUTHOR: JUSTIN FYFE
 * DATE: APRIL 22, 2011
 * FILES:
 *	QDCODEDB.SQL		- SQL CODE TO CREATE TABLES, AND INDICIES FOR SIMEPL CODE DATABASE
 * LICENSE:
 * 	Licensed under the Apache License, Version 2.0 (the "License");
 * 	you may not use this file except in compliance with the License.
 * 	You may obtain a copy of the License at
 *
 *     		http://www.apache.org/licenses/LICENSE-2.0
 *
 * 	Unless required by applicable law or agreed to in writing, software
 * 	distributed under the License is distributed on an "AS IS" BASIS,
 * 	WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * 	See the License for the specific language governing permissions and
 * 	limitations under the License.
 *
 */

 CREATE OR REPLACE FUNCTION make_plpgsql()
RETURNS VOID
LANGUAGE SQL
AS $$
CREATE LANGUAGE plpgsql;
$$;

SELECT
    CASE
    WHEN EXISTS(
        SELECT 1
        FROM pg_catalog.pg_language
        WHERE lanname='plpgsql'
    )
    THEN NULL
    ELSE make_plpgsql() END;
 
DROP FUNCTION make_plpgsql();

-- @SEQUENCE
 -- CONCEPT SEQUENCE
 CREATE SEQUENCE QDCDB_CNCPT_SEQ START WITH 1 INCREMENT BY 1;

 -- @TABLE
 -- CONCEPTS CODE TABLE
 CREATE TABLE QDCDB_CNCPT_CDTBL
 (
	CNCPT_ID	NUMERIC(20,0) NOT NULL DEFAULT nextval('QDCDB_CNCPT_SEQ'),
	CNCPT_MNEMONIC	VARCHAR(10) NOT NULL, -- THE MNEMONIC OF THE CODE
	CNCPT_DISPLAY	VARCHAR(200), -- DISPLAY NAME OF THE CODE
	CNCPT_OID	VARCHAR(48), -- OID OF THE CONCEPT
	CNCPT_PRNT	NUMERIC(20,0), -- PARENT CONCEPT IDENTIFIER
	CONSTRAINT PK_QDCDB_CNCPT_CDTBL PRIMARY KEY (CNCPT_ID),
	CONSTRAINT FK_PRNT FOREIGN KEY (CNCPT_PRNT) REFERENCES QDCDB_CNCPT_CDTBL(CNCPT_ID)
 );

 -- @TABLE
 -- CONCEPT MAPPING TABLE
 CREATE TABLE QDCDB_CNCPT_MAP_TBL
 (
	CNCPT_ID	NUMERIC(20,0) NOT NULL,
	CNCPT_MAP	NUMERIC(20,0) NOT NULL,
	CONSTRAINT PK_CNCPT_MAP_TBL PRIMARY KEY (CNCPT_ID, CNCPT_MAP),
	CONSTRAINT FK_CNCPT_FROM FOREIGN KEY (CNCPT_ID) REFERENCES QDCDB_CNCPT_CDTBL(CNCPT_ID),
	CONSTRAINT FK_CNCPT_TO FOREIGN KEY (CNCPT_MAP) REFERENCES QDCDB_CNCPT_CDTBL(CNCPT_ID)
 );

 -- @INDEX
 -- LOOKUP MAP AND REVERSE MAP
 CREATE INDEX QDCDB_CNCPT_MAP_FROM_IDX ON QDCDB_CNCPT_MAP_TBL(CNCPT_ID);
 CREATE INDEX QDCDB_CNCPT_MAP_TO_IDX ON QDCDB_CNCPT_MAP_TBL(CNCPT_MAP);
 
 -- @INDEX
 -- LOOKUP BY OID/MNEMONIC
 CREATE UNIQUE INDEX QDCDB_CNCPT_MNEMONICIDX ON QDCDB_CNCPT_CDTBL(CNCPT_OID, CNCPT_MNEMONIC);
 
 -- @FUNCTION
 -- LOOKUP CODE
 CREATE OR REPLACE FUNCTION QDCDB_LOOKUP_CD (
	OID_IN		IN VARCHAR(48),
	MNEMONIC_IN	IN VARCHAR(10)
 ) RETURNS SETOF QDCDB_CNCPT_CDTBL AS
 $$
 BEGIN
	RETURN QUERY SELECT * FROM QDCDB_CNCPT_CDTBL WHERE
		CNCPT_MNEMONIC = MNEMONIC_IN AND
		CNCPT_OID = OID_IN;
 END;
 $$ LANGUAGE plpgsql;

 -- @FUNCTION
 -- LOOKUP CODE
 CREATE OR REPLACE FUNCTION QDCDB_GET_CD (
	OID_IN		IN VARCHAR(48),
	MNEMONIC_IN	IN VARCHAR(10)
 ) RETURNS NUMERIC AS
 $$
 BEGIN
	RETURN (SELECT CNCPT_ID FROM QDCDB_CNCPT_CDTBL WHERE
		CNCPT_MNEMONIC = MNEMONIC_IN AND
		CNCPT_OID = OID_IN LIMIT 1);
 END;
 $$ LANGUAGE plpgsql;
 
 -- @FUNCTION
 -- REGISTER CODE
 CREATE OR REPLACE FUNCTION QDCDB_REG_CD (
	MNEMONIC_IN	IN VARCHAR(10),
	OID_IN		IN VARCHAR(48),
	DISPLAY_IN	IN VARCHAR,
	PRNT_IN		IN NUMERIC(20,0)
 ) RETURNS NUMERIC AS 
 $$
 DECLARE 
	CNCPT_ID_VAL	NUMERIC;
 BEGIN
	CNCPT_ID_VAL := nextval('QDCDB_CNCPT_SEQ');
	INSERT INTO QDCDB_CNCPT_CDTBL (CNCPT_ID, CNCPT_MNEMONIC, CNCPT_OID, CNCPT_DISPLAY, CNCPT_PRNT)
		VALUES (CNCPT_ID_VAL, MNEMONIC_IN, OID_IN, SUBSTRING(DISPLAY_IN FROM 0 FOR 199), PRNT_IN);
	RETURN CNCPT_ID_VAL;
 END;
 $$ LANGUAGE plpgsql;

 -- @FUNCTION
 -- GET CHILD CODES
 CREATE OR REPLACE FUNCTION GET_CHILD_CD (
	CNCPT_ID_IN	IN NUMERIC(20,0)
 ) RETURNS SETOF QDCDB_CNCPT_CDTBL AS
 $$
 BEGIN
	RETURN QUERY SELECT * FROM QDCDB_CNCPT_CDTBL WHERE CNCPT_PRNT = CNCPT_ID_IN;
 END
 $$ LANGUAGE plpgsql;

 -- @FUNCTION
 -- REGISTER A FUNCTION MAP
 CREATE OR REPLACE FUNCTION QDCDB_REG_MAP (
	CNCPT_ID_IN	IN NUMERIC(20,0),
	CNCPT_MAP_IN	IN NUMERIC(20,0)
 ) RETURNS VOID AS
 $$
 BEGIN
	INSERT INTO QDCDB_CNCPT_MAP_TBL (CNCPT_ID, CNCPT_MAP) VALUES
		(CNCPT_ID_IN, CNCPT_MAP_IN);
	INSERT INTO QDCDB_CNCPT_MAP_TBL (CNCPT_ID, CNCPT_MAP) VALUES
		(CNCPT_MAP_IN, CNCPT_ID_IN);
 END;
 $$ LANGUAGE plpgsql;

 -- @FUNCTION
 -- LOOKUP A MAP
 CREATE OR REPLACE FUNCTION QDCDB_MAP_CD (
	OID_IN		IN VARCHAR(48),
	MNEMONIC_IN	IN VARCHAR(10),
	TARG_OID_IN	IN VARCHAR(48)
 ) RETURNS SETOF QDCDB_CNCPT_CDTBL AS
 $$
 BEGIN
	RETURN QUERY SELECT * FROM QDCDB_CNCPT_CDTBL WHERE
	CNCPT_ID IN (SELECT CNCPT_MAP FROM QDCDB_CNCPT_MAP_TBL WHERE QDCDB_CNCPT_MAP_TBL.CNCPT_ID = QDCDB_GET_CD(OID_IN,MNEMONIC_IN))
	AND CNCPT_OID = TARG_OID_IN LIMIT 1;
 END
 $$ LANGUAGE plpgsql;
 
 
	
	